﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Script.Serialization;
using System.Data;
using System.Reflection;
using System.Data.SqlClient;
using System.Runtime.CompilerServices;


namespace WcfRestSql
{
    public static class Utilities // extension methods for WcfRestSql service
    {
        /// <summary>
        /// Gen table ra cấu trúc Json
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public static string dataTableToJSON(this DataTable table)
        {
            // REFERENCE: http://stackoverflow.com/questions/2312982/from-datatable-in-c-sharp-net-to-json?rq=1
            List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
            foreach (DataRow row in table.Rows)
            {
                Dictionary<string, object> dict = new Dictionary<string, object>();
                foreach (DataColumn col in table.Columns)
                {
                    dict[col.ColumnName] = row[col];
                }
                list.Add(dict);
            }
            JavaScriptSerializer serializer = new JavaScriptSerializer();
            return serializer.Serialize(list);
        }
        public static List<T> toCollection<T>(this DataTable dt)
        {
            List<T> lst = new System.Collections.Generic.List<T>();
            Type tClass = typeof(T);
            PropertyInfo[] pClass = tClass.GetProperties();
            List<DataColumn> dc = dt.Columns.Cast<DataColumn>().ToList();
            T cn;
            foreach (DataRow item in dt.Rows)
            {
                cn = (T)Activator.CreateInstance(tClass);
                foreach (PropertyInfo pi in pClass)
                {
                    try
                    {
                        DataColumn d = dc.Find(c => c.ColumnName == pi.Name);
                        if (d != null)
                            pi.SetValue(cn, item[pi.Name], null);
                    }
                    catch
                    {
                    }
                }
                lst.Add(cn);
            }
            return lst;
        }
        /// <summary>
        /// Kiểm tra chuỗi là kiểu Date
        /// </summary>
        /// <param name="date"></param>
        /// <returns></returns>
        public static bool isDate(this String date) // is string valid datee?  
        {
            try
            {
                if (string.IsNullOrEmpty(date))
                    return false;
                DateTime dt = DateTime.Parse(date);
                return true;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// Kiểm tra chuỗi là kiểu số
        /// </summary>
        /// <param name="val"></param>
        /// <returns></returns>
        public static bool isNumeric(this String val) // is string valid numeric?  
        {
            if (string.IsNullOrEmpty(val))
                return false;
            Double result;
            return Double.TryParse(val, out result);
        }
        /// <summary>
        /// Kiểm tra chuỗi là kiểu bool
        /// </summary>
        /// <param name="val"></param>
        /// <returns></returns>
        public static bool isBoolean(this String val) // is string valid boolean true/false?  
        {
            if (string.IsNullOrEmpty(val))
                return false;
            if (val.ToLower().Trim() == "false" || val.ToLower().Trim() == "true") // true or false, valid
                return true;
            else
                return false; // will return true even if value is false, tests for valid boolean not t/f
        }

        /// <summary>
        /// Lấy danh sách các params truyền qua Request
        /// </summary>
        /// <param name="cmd"></param>
        /// <returns></returns>
        public static List<SqlParameter> getSqlParametersFromHttpRequest(this wcf_rest_sqlcommand cmd)
        {
            // get sql parameters from delimited paramnames/paramvalues 
            // in http client payload wcf_rest_sqlcommand
            char seperator = '|'; // used to seperate parameter names/values
            List<SqlParameter> sqlparams = new List<SqlParameter>();
            if (!string.IsNullOrEmpty(cmd.paramnames) || !string.IsNullOrEmpty(cmd.paramvalues))
            {
                string[] paramnamesArray = cmd.paramnames.Split(seperator);
                string[] paramvaluesArray = cmd.paramvalues.Split(Convert.ToChar(seperator));
                if (paramnamesArray.Count() == paramvaluesArray.Count()) // need to match count
                {
                    for (int i = 0; i < paramnamesArray.Count(); i++)
                    {
                        if (!string.IsNullOrEmpty(paramnamesArray[i]) && !string.IsNullOrEmpty(paramvaluesArray[i]))
                        {
                            SqlParameter p = new SqlParameter();
                            p.ParameterName = paramnamesArray[i].Trim();
                            p.Value = paramvaluesArray[i].Trim();
                            sqlparams.Add(p);
                        }
                    }
                }
            }
            return sqlparams;
        }


        /// <summary>
        /// Xử lý, gán các params từ httpRequest truyền lên, gán vào SqlCommand
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="sqlcmd"></param>
        /// <param name="batchIdx"></param>
        /// <returns></returns>
        public static SqlCommand processSqlParameters(this SqlCommand cmd, wcf_rest_sqlcommand sqlcmd, int batchIdx = 0)
        {
            // update SqlCommand with parameter info from wcf_rest_sqlcommand
            SqlCommandBuilder.DeriveParameters(cmd);
            SqlParameterCollection spc = cmd.Parameters; // params from stored proc

            //Execute truyền tên biến
            if (!String.IsNullOrEmpty(sqlcmd.paramnames))
            {

                List<SqlParameter> spparams = sqlcmd.getSqlParametersFromHttpRequest();

                List<SqlParameter> newParams = new List<SqlParameter>();
                if (spparams != null && spparams.Count() > 0)
                {
                    foreach (SqlParameter p in spparams)
                    {
                        SqlParameter newP = p; // alter new param, can't alter p during enumeration of it's collection
                        string s = p.Value.ToString();// from client, all param values will be string
                        if (!string.IsNullOrEmpty(p.ParameterName)) //&& !string.IsNullOrEmpty(p.Value.ToString()))
                        {
                            // check parameters for stored proc retrieved from sql server to set direction
                            foreach (SqlParameter checkP in spc)
                            {
                                if (p.ParameterName.ToLower() == checkP.ParameterName.ToLower())
                                {
                                    //p.Direction = checkP.Direction;
                                    newP.Direction = checkP.Direction;
                                    switch (checkP.DbType) // create form input control based on param type
                                    {
                                        case DbType.String: // it's a string, just retain value
                                            break;
                                        case DbType.DateTime: // insure valid date string
                                            if (string.IsNullOrEmpty(s) || !s.isDate()) // use extension method
                                                newP.Value = null; // s = DateTime.Now.ToString();
                                            else
                                                newP.Value = Convert.ToDateTime(s);
                                            break;
                                        case DbType.Int16:
                                        case DbType.Int32:
                                        case DbType.Int64: // insure it's numeric
                                            if (string.IsNullOrEmpty(s) || !s.isNumeric())
                                                newP.Value = null; //s = "0";
                                            else
                                                newP.Value = Convert.ToInt32(s);
                                            break;
                                        case DbType.Boolean:
                                            if (string.IsNullOrEmpty(s) || !s.isBoolean())
                                                newP.Value = false; //s = "0";
                                            else
                                                newP.Value = s.ToLower() == "true" ? true : false;
                                            break;

                                        case DbType.Double:
                                        case DbType.Decimal:
                                        case DbType.Currency: // insure numeric
                                            if (string.IsNullOrEmpty(s) || s.isNumeric())
                                                newP.Value = null; //s = "0.00";
                                            else
                                                newP.Value = Convert.ToDecimal(s);
                                            break;
                                    }
                                    newParams.Add(newP);
                                }
                            }
                        }
                    }
                }
                cmd.Parameters.Clear();
                foreach (SqlParameter sp in newParams)
                    cmd.Parameters.Add(sp);
            }
            else
            {
                int idxValue = 0;
                if (batchIdx > 0)
                {
                    idxValue = (sqlcmd.paramlist.Length / sqlcmd.numberbatch) * batchIdx;
                }

                //
                for (int i = 0; i < cmd.Parameters.Count; i++)
                {
                    if (cmd.Parameters[i].Direction == ParameterDirection.ReturnValue) //--Ko tính params return value của procedure
                        continue;

                    //Cac bien khong truyen sau, co the nhan gia tri NULL
                    if (idxValue >= sqlcmd.paramlist.Length)
                        continue;

                    //
                    object sValue = sqlcmd.paramlist[idxValue];
                    idxValue++;

                    //cmd.Parameters[i].Value = paramvaluesArray[i];
                    switch (cmd.Parameters[i].DbType) // create form input control based on param type
                    {
                        case DbType.AnsiString:
                        case DbType.String: // it's a string, just retain value
                        case DbType.AnsiStringFixedLength:
                        case DbType.StringFixedLength:
                            cmd.Parameters[i].Value = sValue;
                            break;
                        case DbType.DateTime: // insure valid date string
                            try
                            {
                                if (sqlcmd.jsonformat)
                                {
                                    try
                                    {
                                        if (sValue == null || sValue.ToString() == "")
                                            sValue = DBNull.Value;
                                        else
                                        {
                                            string jsonDate = sValue.ToString().Replace("/Date(", string.Empty); jsonDate = jsonDate.Replace(")/", string.Empty);
                                            sValue = new DateTime(1970, 1, 1, 0, 0, 0, 0, DateTimeKind.Utc).AddMilliseconds(long.Parse(jsonDate));
                                            //
                                            if ((DateTime)sValue <= (DateTime)System.Data.SqlTypes.SqlDateTime.MinValue)
                                                sValue = DBNull.Value;
                                        }

                                        //sValue = new JavaScriptSerializer().Deserialize<DateTime>(sValue.ToString());

                                        //if(!String.IsNullOrEmpty(jsonDate))
                                        //{
                                        //    sValue = UNIXTimeToDateTime( Convert.ToDouble(jsonDate));
                                        //}
                                    }
                                    catch (Exception ex) { sValue = Convert.ToDateTime(sValue); }
                                }
                                else
                                {
                                    sValue = Convert.ToDateTime(sValue);
                                }
                                //
                                cmd.Parameters[i].Value = sValue;
                            }
                            catch (Exception)
                            {
                                cmd.Parameters[i].Value = DBNull.Value;
                            }
                            break;
                        case DbType.Int16:
                        case DbType.Int32:
                        case DbType.Int64: // insure it's numeric
                            if (sValue == null)
                                cmd.Parameters[i].Value = DBNull.Value;
                            else
                            cmd.Parameters[i].Value = Convert.ToInt32(sValue);
                            break;
                        case DbType.Boolean:
                            cmd.Parameters[i].Value = Convert.ToBoolean(sValue);
                            break;
                        case DbType.Double:
                        case DbType.Decimal:
                        case DbType.Currency: // insure numeric
                            cmd.Parameters[i].Value = Convert.ToDecimal(sValue);
                            break;
                    }
                }
            }
            return cmd;
        }


        /// <summary>
        /// Chuyển từ định dạng ngày giờ trong JSON sang DateTime C#
        /// </summary>
        /// <param name="unix_time"></param>
        /// <returns></returns>
        public static DateTime UNIXTimeToDateTime(double unix_time)
        {
            return new DateTime(1970, 1, 1, 0, 0, 0, 0, DateTimeKind.Utc).AddMilliseconds(unix_time).ToLocalTime();
        }
    }
}